package pc.util;

import java.util.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.util.*;
import com.idautomation.linear.*;
import com.idautomation.linear.encoder.*;

import pc.system.*;

public class ExcelFile extends Actions
{
  private boolean zero_blank = true;
  private static final String file_separator = System.getProperty("file.separator");

  private String work_path = null;
  private String templete = null;
  private FileInputStream fin  = null;
  private POIFSFileSystem fsys = null;

  private HSSFWorkbook workbook  = null;
  private HSSFSheet    sheet     = null;
  private Hashtable    cellStyles = new Hashtable();
  private int          colCount  = 0;

  private int  dy  = 0;
  //
  public static final short BORDER_THIN   = HSSFCellStyle.BORDER_THIN;
  public static final short BORDER_MEDIUM = HSSFCellStyle.BORDER_MEDIUM;
  public static final short BORDER_THICK  = HSSFCellStyle.BORDER_THICK;
  public static final short BORDER_NONE   = HSSFCellStyle.BORDER_NONE;
  //
  public static final short VERTICAL_TOP    = HSSFCellStyle.VERTICAL_TOP;
  public static final short VERTICAL_BOTTOM = HSSFCellStyle.VERTICAL_BOTTOM;
  public static final short VERTICAL_CENTER = HSSFCellStyle.VERTICAL_CENTER;
  //
  private static final Hashtable color_table = new Hashtable();
  //
  public ExcelFile(Actions owner,String templete,int sheet_no, String work_path)
  {

    super(owner);

    color_table.put("RED",new HSSFColor.RED());
    color_table.put("BLUE",new HSSFColor.BLUE());
    color_table.put("WHITE",new HSSFColor.WHITE());
    color_table.put("GREEN",new HSSFColor.GREEN());
    color_table.put("YELLOW",new HSSFColor.YELLOW());
    color_table.put("BROWN",new HSSFColor.BROWN());
    color_table.put("BLACK",new HSSFColor.BLACK());

    this.work_path = work_path;

    this.templete = templete;
    String filename = getAsbPath("reports",templete);
    try {
      fin  = new java.io.FileInputStream(filename);
      fsys = new POIFSFileSystem(fin);
      workbook = new HSSFWorkbook( fsys );
      setSheet(sheet_no);
    } catch ( java.io.IOException ex ) {
      System.out.println(ex.getMessage());
      ex.printStackTrace();
    }
  }

  public ExcelFile(Actions owner,String templete, String work_path)
  {
    this(owner,templete,0,work_path);
  }

  public void setZeroBlank(boolean zero_blank)
  {
    this.zero_blank = zero_blank;
  }

  public String getTemplete()
  {
    return templete;
  }

  public void close()
  {
    try {
      if (fin != null) fin.close();
    } catch (IOException e) {
      System.out.println(e.getMessage());
    }
  }

  public int getFirstRow(String sign)
  {
    cellStyles.clear();
    int dy = 0;
    for (int i = 0; i < 50; i++) {
      HSSFCell cell = getCell(0,i);
      if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().equals(sign)) {
          cell.setCellValue("");
          dy = i;
          break;
        }
      }
    }

    if (dy > 0) colCount  = getRow(dy).getPhysicalNumberOfCells();

    for (int i = 0; i < colCount; i++) {
      HSSFCell cell = getCell(i,dy);
      cellStyles.put("template"+i,cell.getCellStyle());
      cell.setCellValue("");
    }
    return dy;
  }

  public int getFirstRow()
  {
    return dy;
  }

  private HSSFFont getCellFont(HSSFCellStyle style)
  {
    return workbook.getFontAt(style.getFontIndex());
  }

  public HSSFFont getCellFont(int x, int y)
  {
    return  getCellFont(getCellStyle(x,y));
  }

  public HSSFCellStyle getCellStyle(int x, int y)
  {
    return getCell(x,y).getCellStyle();
  }

  public HSSFCellStyle getCellStyle(String name)
  {
    HSSFCellStyle style = (HSSFCellStyle)cellStyles.get(name);
    if (style == null) {
      style = workbook.createCellStyle();
      style.setFont(workbook.createFont());
      cellStyles.put(name,style);
    }
    return style;
  }

  public HSSFCellStyle getCellStyle(int x)
  {
    return getCellStyle("template" + x);
  }

  public String getAsbPath(String path,String filename)
  {
    if (path == null) return filename;
    else return  work_path + file_separator + path + file_separator + filename;
  }

  public String getAsbPath(String filename)
  {
    return  work_path + file_separator + "temp" + file_separator + filename;
  }

  public HSSFWorkbook getWorkBook()
  {
    return workbook;
  }

  public HSSFSheet getSheet(int no)
  {
    HSSFSheet sheet = workbook.getSheetAt(no);
    if (sheet == null) sheet = workbook.createSheet();
    return sheet;
  }

  public HSSFSheet getSheet()
  {
    return sheet;
  }

  public void setSheet(HSSFSheet sheet)
  {
    this.sheet = sheet;
  }

  public void setColCount(int colCount)
  {
    this.colCount = colCount;
  }

  public void setSheet(int sheet_no)
  {
    this.sheet = getSheet(sheet_no);
    dy = getFirstRow("***");
    /*
    cellStyles.clear();

    this.sheet = getSheet(sheet_no);

    for (int i = 0; i < 50; i++) {
      HSSFCell cell = getCell(0,i);
      if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().equals("***")) {
          cell.setCellValue("");
          dy = i;
          break;
        }
      }
    }

    if (dy > 0) colCount  = getRow(dy).getPhysicalNumberOfCells();

    for (int i = 0; i < colCount; i++) {
      HSSFCell cell = getCell(i,dy);
      cellStyles.put("template"+i,cell.getCellStyle());
      cell.setCellValue("");
    } */
  }

  public HSSFSheet newSheet()
  {
    return workbook.cloneSheet(0);
  }

  public void setSheetName(int sheet_no,String name)
  {
    workbook.setSheetName(sheet_no,name);
  }

  public void removeSheetAt(int sheet_no)
  {
    workbook.removeSheetAt(sheet_no);
  }

  public HSSFRow getRow(int y)
  {
    HSSFRow row = sheet.getRow(y);
    if (row == null) row = sheet.createRow(y);
    return row;
  }

  public HSSFCell getCell(int x,int y)
  {
    HSSFCell cell = getRow(y).getCell((short)x);
    if (cell == null) cell = getRow(y).createCell( (short) x);
    return cell;
  }

  public void setBgColor(int x, int y, int color)
  {
    HSSFCell cell = this.getCell(x,y);
    HSSFCellStyle style = this.getCellStyle("bgcolor" + x);//y*100 + x);//this.getWorkBook().createCellStyle();
    this.copyStyleFromCell(style,x,y);
    style.setFillForegroundColor((short)color);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(style);
  }

  public void mergeCells(int x1,int y1, int x2, int y2)
  {
    this.getSheet().addMergedRegion(new Region(y1,(short)x1,y2,(short)x2));
  }

  public void lineV(int x,int y1,int y2, short style_left, short style_right)
  {
    for (int y = y1; y <= y2; y++) {
      HSSFCell cell = getCell(x,y);
      HSSFCellStyle style = this.getWorkBook().createCellStyle();
      copyStyleFromCell(style,x,y);
      style.setBorderLeft(style_left);
      style.setBorderRight(style_right);
      cell.setCellStyle(style);
    }
  }

  public void line(int y,int x1,int x2, short style_top, short style_bottom)
  {
    for (int x = x1; x <= x2; x++) {
      String s = "line" + style_top + "-" + style_bottom + "-" + x;
      HSSFCellStyle style = getCellStyle(s);
      if (getRow(y).getCell((short)x) != null) copyStyleFromCell(style,x,y);
      style.setBorderTop(style_top);
      style.setBorderBottom(style_bottom);
      getCell(x,y).setCellStyle(style);
    }
  }

  public void line(int y,int from_x, short style_top, short style_bottom)
  {
    line(y,from_x,colCount-1,style_top,style_bottom);
  }

  public void line(int y, short style_top, short style_bottom)
  {
    line(y,0,colCount-1,style_top,style_bottom);
  }

  private HSSFCell setCellStyle(int x, int y)
  {
    HSSFCell cell = getCell(x,y);

    if (colCount > 0) {
      HSSFCellStyle style = getCellStyle(x);
      cell.setCellStyle(style);
    }
    return cell;
  }

  private HSSFFont getFont(HSSFCellStyle style)
  {
    return workbook.getFontAt(style.getFontIndex());
  }

  public void setCellTextColor(int x, int y, String color)
  {
    color = color.toUpperCase();
    HSSFColor cl = (HSSFColor)color_table.get(color);
    if (cl == null) return;

    HSSFCell cell = getCell(x,y);

    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    style.setFont(font);

    copyStyleFromCell(style,x,y);

    font.setColor(cl.getIndex());//HSSFColor.RED.index);
    cell.setCellStyle(style);
  }

  public void setRowTextColor(int y, String color)
  {
    color = color.toUpperCase();
    HSSFColor cl = (HSSFColor)color_table.get(color);
    if (cl == null) return;

    for (int x = 0; x < colCount; x++)  {
      HSSFCellStyle style = this.getCellStyle("row_color" + x);
      copyStyle(style,this.getCellStyle(x));
      this.getFont(style).setColor(cl.getIndex());
      HSSFCell cell = getCell(x,y);
      cell.setCellStyle(style);
    }
  }

  private void copyStyle(HSSFCellStyle style,HSSFCellStyle st)
  {
    style.setDataFormat(st.getDataFormat());
    getFont(style).setBoldweight(getFont(st).getBoldweight());
    getFont(style).setItalic(getFont(st).getItalic());
    getFont(style).setFontHeight(getFont(st).getFontHeight());
    style.setAlignment(st.getAlignment());
    style.setBorderBottom(st.getBorderBottom());
    style.setBorderLeft(st.getBorderLeft());
    style.setBorderRight(st.getBorderRight());
    style.setBorderTop(st.getBorderTop());
    style.setWrapText(st.getWrapText());
  }

  private void copyStyleFromCell(HSSFCellStyle style,int x, int y)
  {
    copyStyle(style,getCell(x,y).getCellStyle());
  }

  public HSSFCellStyle setCellStyle(int x, int y, String at)
  {
    HSSFCellStyle style = getCellStyle(at+x);
    copyStyleFromCell(style,x,y);
    if (at == null) at = "";
    if (at.indexOf('B') >= 0) {
      getCellFont(style).setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    }
    if (at.indexOf('I') >= 0) getCellFont(style).setItalic(true);
    int idx = at.indexOf('=');
    if (idx >= 0) {
      short size = Short.parseShort(at.substring(idx+1));
      getCellFont(style).setFontHeightInPoints(size);
    }
    getCell(x,y).setCellStyle(style);
    return style;
  }
  /*-------------------------------------------------*/
  public void setCellValue(int x, int y, String value)
  {
    //setCellStyle(x,y);
    HSSFCell cell = setCellStyle(x,y);
    if (value != null) cell.setCellValue(value);
    else cell.setCellValue("");
  }

  public void setCellValue(int x, int y, java.sql.Date value)
  {
    //setCellStyle(x,y);
    HSSFCell cell = setCellStyle(x,y);
    if (value == null) {
      cell.setCellValue("");
      return;
    }
    if (value.compareTo(Utils.nullDate) == 0) {
      cell.setCellValue("");
      return;
    }
    cell.setCellValue(value);
  }

  public void setCellValue(int x, int y, java.sql.Time value)
  {
    HSSFCell cell = setCellStyle(x,y);
    if (value == null) {
      cell.setCellValue("");
      return;
    }
    if (value.compareTo(Utils.nullTime) == 0) {
      cell.setCellValue("");
      return;
    }
    cell.setCellValue(value);
  }

  public void setCellValue(int x, int y, double value)
  {
    //setCellStyle(x, y);
    HSSFCell cell = setCellStyle(x,y);
    if (value != 0 || !zero_blank)  cell.setCellValue(value);
    //else cell.setCellValue("");
  }

  public void setCellValue(int x, int y, float value)
  {
    //setCellStyle(x, y);
    HSSFCell cell = setCellStyle(x,y);
    if (value != 0 || !zero_blank) cell.setCellValue(value);
    //else cell.setCellValue("");
  }
  /*-------------------------------------------------*/
  public void set_CellFormula(int x, int y, String value)
  {
    HSSFCell cell = getCell(x,y);
    if (value != null) cell.setCellFormula(value);
    else cell.setCellValue("");
  }
  /*-------------------------------------------------*/
  public void set_CellValue(int x, int y, String value)
  {
    //setCellStyle(x,y);
    HSSFCell cell = getCell(x,y);
    if (value != null) cell.setCellValue(value);
    else cell.setCellValue("");
  }

  public void set_CellValue(int x, int y, java.sql.Date value)
  {
    //setCellStyle(x,y);
    HSSFCell cell = getCell(x,y);
    if (value == null) {
      cell.setCellValue("");
      return;
    }
    if (value.compareTo(Utils.nullDate) == 0) {
      cell.setCellValue("");
      return;
    }
    cell.setCellValue(value);
  }

  public void set_CellValue(int x, int y, java.sql.Time value)
  {
    HSSFCell cell = getCell(x,y);
    if (value == null) {
      cell.setCellValue("");
      return;
    }
    if (value.compareTo(Utils.nullTime) == 0) {
      cell.setCellValue("");
      return;
    }
    cell.setCellValue(value);
  }

  public void set_CellValue(int x, int y, double value)
  {
    HSSFCell cell = getCell(x,y);
    if (value != 0 || !zero_blank)  cell.setCellValue(value);
    //else cell.setCellValue("");
  }

  public void set_CellValue(int x, int y, float value)
  {
    HSSFCell cell = getCell(x,y);
    //if (value != 0 || !zero_blank) cell.setCellValue(value);
    cell.setCellValue(value);
    //else cell.setCellValue("");
  }

  public void set_CellPercent(int x, int y, float value)
  {
    set_CellValue(x,y,Utils.numberToStr(value,2));
  }
  /*-------------------------------------------------*/
  public void setCellStyleFrom(int x,int y,int fx, int fy)
  {
    HSSFCellStyle st = this.getCell(fx,fy).getCellStyle();
    this.getCell(x,y).setCellStyle(st);
  }

  public void setCellStyleFrom(int x,int y,int from)
  {
    setCellStyleFrom(x,y,x,from);
  }

  public void setRowStyleFrom(int y,int from)
  {
    for (int i = 0; i < colCount; i++) {
      setCellStyleFrom(i,y,from);
    }
  }

  public void copyRow(int from, int to)
  {
    for (int i = 0; i < colCount; i++) {
      this.set_CellValue(i,to,this.getCell(i,from).getStringCellValue());
    }
    this.getRow(to);
    setRowStyleFrom(to,from);
  }
  /*-------------------------------------------------*/
  public void shiftRows(int y1,int y2,int skip)
  {
    sheet.shiftRows(y1,y2,skip,false,false);
  }

  public void shift_Rows(int x1,int x2,int skip)
  {
    sheet.shiftRows(x1,x2,skip);
  }

  public void removeRow(int y)
  {
    sheet.removeRow(getRow(y));
  }

  public String export(String filename,String path)
  {
    String file_tmp = getAsbPath(path,filename);
    if (workbook == null) return null;
    try {
      FileOutputStream file = new FileOutputStream(file_tmp);
      workbook.write(file);
      file.close();
      fin.close();
    }  catch (java.io.IOException ex) {
      //System.out.println(ex.getMessage());
      ex.printStackTrace();
      return null;
    }
    if (path == null) return filename;
    else return path + "/" + filename;
  }

  public String export(String filename)
  {
    return export(filename,"temp");
  }

  public String getFileOut()
  {
    //SimpleDateFormat dateFormat = new SimpleDateFormat("yyMMdd");
    //SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
    String s = templete.toLowerCase();
    int p = s.lastIndexOf(".xls");
    String us = ((Auth)getRoot()).getUserName();
    int no = ((Auth)getRoot()).getCount();
    if (p > 0) {
      s = us + "_" + templete.substring(0,p) + "_" + no + templete.substring(p);
      //s.replaceFirst(".xls", "_" + getRoot().getOID());
    } else s = templete + "_" + no;
    return s;
  }

  public String export()
  {
    return export(getFileOut());
  }

  public void createBarCode(int x,int y,String value)
  {
    int    no = ((Auth)getRoot()).getCount();
    String us = ((Auth)getRoot()).getUserName();
    String jpeg = getAsbPath("temp","bc_" + us + no + ".jpeg");

    BarCode bc = new BarCode();
    bc.barType=BarCode.CODE128;
    bc.setAutoSize(true);
    bc.setDataToEncode(value);
    bc.setAutoSize(true);
    //String jpeg = getAsbPath("temp","barcode_" + no + ".jpeg");

    barCodeEncoder bce = new barCodeEncoder(bc,"JPEG", jpeg);

    if (!bce.result) {
      System.out.println("Error creating barcode");
      return;
    }

    //System.out.println("Create barcode jpeg file OK");

    byte buff[] = new byte[16000];
    try {
      InputStream fileIn = new FileInputStream(jpeg);
      int i = fileIn.read(buff);
      fileIn.close();
    } catch (IOException e) {
      System.out.println(e.getMessage());
      return;
    }

    this.getCell(x,y);
    short w = sheet.getColumnWidth((short)x);

    sheet.setColumnWidth((short)x,(short)(bc.getWidth()*36.6));//6192
    sheet.getRow(y).setHeightInPoints(bc.getHeight()-16);
    int pic_no = workbook.addPicture(buff,workbook.PICTURE_TYPE_JPEG);

    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFClientAnchor anchor = new HSSFClientAnchor();//0,0,0,0,(short)0,0,(short)1,1);
    anchor.setAnchor((short)x,y,0,0,(short)x,y,1023,255);
    anchor.setAnchorType(3);
    HSSFPicture pic = patriarch.createPicture(anchor,pic_no);
  }

  
}
